import os
import xlwt
import xlrd
from xlutils.copy import copy


class SqlTransferToTestcase:
    def __init__(self, sql_path, expect_path, tar_file):
        self.sqls_path = sql_path
        self.expects_path = expect_path
        self.targ_file = tar_file
        # 姓首字母+8位工号
        self.author = "*********"

    def sql_transfer_to_testcase(self):
        """
        将sql用例转化成测试用例excel格式，方便回传云龙
        :return:
        """
        # 循环遍历sql和expect的文件，提取相关信息写入到excel表格中
        work_book = xlrd.open_workbook(filename=self.targ_file)
        excel_file = copy(work_book)
        sheet = excel_file.get_sheet(0)
        row = 7
        # columes代表需需要填入的列
        columes = [0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 40, 42]
        if os.path.exists(self.sqls_path) \
                and os.path.exists(self.expects_path):
            for file in os.listdir(self.sqls_path):
                sql_path = "/".join([self.sqls_path, file])
                expect_path = "/".join([self.expects_path, file[:-4]])
                if os.path.isfile(sql_path) and os.path.isfile(expect_path):
                    # 获取sql内容
                    with open(sql_path, 'r', encoding="utf-8") as f:
                        sql_data = f.readlines()
                    # 获取expect内容
                    with open(expect_path, 'r', encoding="utf-8") as f:
                        expect_data = f.readlines()

                    testcase_depth = "......"
                    testcase_name = sql_data[2][15:].split()
                    testcase_num = file[:-4]
                    testcase_stage = "Beta"
                    testcase_autotype = "true"
                    testcase_automated = "true"
                    testcase_level = "level 1"
                    testcase_pre = "数据库状态正常"
                    testcase_step = "".join(sql_data[4:])
                    testcase_expect = "'" + "".join(expect_data[3:])
                    testcase_author = self.author
                    testcase_designer = self.author

                    testcase_info = [testcase_depth, testcase_name,
                                     testcase_num, testcase_stage,
                                     testcase_autotype, testcase_automated,
                                     testcase_level, testcase_pre,
                                     testcase_step, testcase_expect,
                                     testcase_author, testcase_designer]
                    i = 0
                    while i < len(columes):
                        sheet.write(row, columes[i], testcase_info[i])
                        i += 1
                    row += 1
        excel_file.save(self.targ_file)


if __name__ == '__main__':
    # sql文件所在目录
    sql_file_folder = r"sql_file_folder"
    # 期望结果文件所在目录
    expect_file_folder = r"expect_file_folder"
    # 云龙导出的用例模板，注意层级
    target_file = r"target_file.xls"
    obj = SqlTransferToTestcase(sql_path=sql_file_folder,
                                expect_path=expect_file_folder,
                                tar_file=target_file)
    obj.sql_transfer_to_testcase()
